﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Management_System_Database
{
    public partial class Fr_LostUpdate : Form
    {
        public Fr_LostUpdate()
        {
            InitializeComponent();
        }
        // load dữ liệu tìm kiếm lên 
        public void loadDataLostUpdateError()
        {
            string sophong = cb_lostupdate_error_sophong.Text;
            string phim = cb_lostupdate_namefilm_error.Text;
            string gio = cb_lostupdate_time_error.Text;
            try
            {
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect;
                DataTable d = new DataTable();
                string strsql = "select ROW_NUMBER() OVER (ORDER BY v.MaVe) AS STT,v.MaVe,v.STTChoNgoi,v.TinhTrangVe,v.NguoiLon_TreEm,v.MaHD,v.MaCTSuatChieu,v.MaDayPC from Ve v,Phim p,SuatChieu s,DayGhe_PhongChieu d,CTSuatChieu c";
                strsql = strsql + " where v.MaDayPC = d.MaDayPC and d.SoPhong = '" + sophong + "' and v.MaCTSuatChieu = c.MaCTSuatChieu and c.MaPhim = p.MaPhim and p.TenPhim = '" + phim + "' and c.MaSuatChieu  = s.MaSuatChieu ";
                strsql = strsql + " and s.ThoiGianBatDau = '" + gio +"'" ;
               
                comm.CommandText = strsql;
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                dgw_lostupdate_error.DataSource = d;
                if (d.Rows.Count == 0)
                {
                    MessageBox.Show("Không tìm thấy!","Thông Báo");
                }
                else
                {
                    dgw_lostupdate_error.DataSource = d;

                    int n = dgw_lostupdate_error.Rows.Count - 1;
                    for (int i = 0; i < n; i++)
                    {
                        dgw_lostupdate_error.Rows[i].Cells[0].Value = 0;
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
                         
        }
        // load dữ liệu tìm kiếm lên 
        public void loadDataLostUpdateFix()
        {
            string sophong = cb_lostupdate_fix_sophong.Text;
            string phim = cb_lostupdate_namefilm_fix.Text;
            string gio = cb_lostupdate_time_fix.Text;
            try
            {
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect;
                DataTable d = new DataTable();
                string strsql = "select ROW_NUMBER() OVER (ORDER BY v.MaVe) AS STT,v.MaVe,v.STTChoNgoi,v.TinhTrangVe,v.NguoiLon_TreEm,v.MaHD,v.MaCTSuatChieu,v.MaDayPC from Ve v,Phim p,SuatChieu s,DayGhe_PhongChieu d,CTSuatChieu c";
                strsql = strsql + " where v.MaDayPC = d.MaDayPC and d.SoPhong = '" + sophong + "' and v.MaCTSuatChieu = c.MaCTSuatChieu and c.MaPhim = p.MaPhim and p.TenPhim = '" + phim + "' and c.MaSuatChieu  = s.MaSuatChieu ";
                strsql = strsql + " and s.ThoiGianBatDau = '" + gio + "'";

                comm.CommandText = strsql;
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                dgw_lostudate_fix.DataSource = d;
                if (d.Rows.Count == 0)
                {
                    MessageBox.Show("Không tìm thấy!", "Thông Báo");
                }
                else
                {
                    dgw_lostudate_fix.DataSource = d;
                    int n = dgw_lostudate_fix.Rows.Count - 1;
                    for (int i = 0; i < n; i++)
                    {

                        dgw_lostudate_fix.Rows[i].Cells[0].Value = 0;
                    }
                }

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
        }
        // load dữ liệu tìm kiếm lên 
        public void loadDataLostUpdateDeadlockError()
        {
            string sophong = cb_lostupdate_deadlock_error_sophong.Text;
            string phim = cb_lostupdate_deadlock_namefilm_error.Text;
            string gio = cb_lostupdate_deadlock_time_error.Text;
            try
            {
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect;
                DataTable d = new DataTable();
                string strsql = "select ROW_NUMBER() OVER (ORDER BY v.MaVe) AS STT,v.MaVe,v.STTChoNgoi,v.TinhTrangVe,v.NguoiLon_TreEm,v.MaHD,v.MaCTSuatChieu,v.MaDayPC from Ve v,Phim p,SuatChieu s,DayGhe_PhongChieu d,CTSuatChieu c";
                strsql = strsql + " where v.MaDayPC = d.MaDayPC and d.SoPhong = '" + sophong + "' and v.MaCTSuatChieu = c.MaCTSuatChieu and c.MaPhim = p.MaPhim and p.TenPhim = '" + phim + "' and c.MaSuatChieu  = s.MaSuatChieu ";
                strsql = strsql + " and s.ThoiGianBatDau = '" + gio + "'";

                comm.CommandText = strsql;
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                dgw_lostupdate_deadlock_error.DataSource = d;
                if (d.Rows.Count == 0)
                {
                    MessageBox.Show("Không tìm thấy!", "Thông Báo");
                }
                else
                {
                    dgw_lostupdate_deadlock_error.DataSource = d;
                    int n = dgw_lostupdate_deadlock_error.Rows.Count - 1;
                    for (int i = 0; i < n; i++)
                    {

                        dgw_lostupdate_deadlock_error.Rows[i].Cells[0].Value = 0;
                    }
                }

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
        }
        //thực hiện hành động đặt vé
        private void btn_update_Click(object sender, EventArgs e)
        {
            try
            {
                int n = dgw_lostupdate_error.Rows.Count - 1;
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();

                comm.Connection = connect;
                for (int i = 0; i < n; i++)
                {


                   if (dgw_lostupdate_error.Rows[i].Cells[0].Value.Equals("1"))
                    {

                        string maVe = dgw_lostupdate_error.Rows[i].Cells[2].Value.ToString();
                        string TinhTrang = dgw_lostupdate_error.Rows[i].Cells[4].Value.ToString();
                        string MaHD = dgw_lostupdate_error.Rows[i].Cells[6].Value.ToString();
                        string NguoiLonhayTreEm = dgw_lostupdate_error.Rows[i].Cells[5].Value.ToString();
                        comm.CommandText = "sp_LostUpdate_error";
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                        comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                        comm.Parameters["@MaVe"].Value = maVe;

                        comm.Parameters.Add(new SqlParameter("@LoaiVe", SqlDbType.Bit));
                        comm.Parameters["@LoaiVe"].Direction = ParameterDirection.Input;
                        comm.Parameters["@LoaiVe"].Value = NguoiLonhayTreEm;

                        comm.Parameters.Add(new SqlParameter("@output", SqlDbType.VarChar, 100));
                        comm.Parameters["@output"].Direction = ParameterDirection.Output;
                        comm.Parameters["@output"].Value = "";
                        comm.ExecuteNonQuery();
                        string result = comm.Parameters["@output"].Value.ToString();
                       MessageBox.Show(result, "Thông Báo");
                       loadDataLostUpdateError();
                       
                    }
                }
                comm.Dispose();
                
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông Báo");
            }
        }

        private void btn_cancle_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        // load dữ liệu vào các combobox khi form đc load lên
        private void Fr_LostUpdate_Load(object sender, EventArgs e)
        {
            try
            {
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect;
                DataTable d = new DataTable();
                comm.CommandText = "select TenPhim from PHIM ";
                SqlDataAdapter adap = new SqlDataAdapter(comm);
                adap.Fill(d);
                cb_lostupdate_namefilm_error.DataSource = d;
                cb_lostupdate_namefilm_error.DisplayMember = "TenPhim";
                cb_lostupdate_namefilm_fix.DataSource = d;
                cb_lostupdate_namefilm_fix.DisplayMember = "TenPhim";
                cb_lostupdate_deadlock_namefilm_error.DataSource = d;
                cb_lostupdate_deadlock_namefilm_error.DisplayMember = "TenPhim";



            }

            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
            try
            {
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();
                comm.Connection = connect;
                DataTable d1 = new DataTable();
                comm.CommandText = "select distinct ThoiGianBatDau from SUATCHIEU ";
                SqlDataAdapter adap1 = new SqlDataAdapter(comm);
                adap1.Fill(d1);

                cb_lostupdate_time_error.DataSource = d1;
                cb_lostupdate_time_error.DisplayMember = "ThoiGianBatDau";
                cb_lostupdate_time_fix.DataSource = d1;
                cb_lostupdate_time_fix.DisplayMember = "ThoiGianBatDau";
                cb_lostupdate_deadlock_time_error.DataSource = d1;
               cb_lostupdate_deadlock_time_error.DisplayMember = "ThoiGianBatDau";
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }


         
         
        }

        private void btn_cancle_fix_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        //thực hiện hành động đặt vé
        private void btn_update_fix_Click(object sender, EventArgs e)
        {
            try
            {
                int n = dgw_lostudate_fix.Rows.Count - 1;
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();

                comm.Connection = connect;
                for (int i = 0; i < n; i++)
                {


                    if (dgw_lostudate_fix.Rows[i].Cells[0].Value.Equals("1"))
                    {

                        string maVe = dgw_lostudate_fix.Rows[i].Cells[2].Value.ToString();
                        string TinhTrang = dgw_lostudate_fix.Rows[i].Cells[4].Value.ToString();
                        string MaHD = dgw_lostudate_fix.Rows[i].Cells[6].Value.ToString();
                        string NguoiLonhayTreEm = dgw_lostudate_fix.Rows[i].Cells[5].Value.ToString();
                        comm.CommandText = "sp_LostUpdate_fix";
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                        comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                        comm.Parameters["@MaVe"].Value = maVe;

                        comm.Parameters.Add(new SqlParameter("@LoaiVe", SqlDbType.Bit));
                        comm.Parameters["@LoaiVe"].Direction = ParameterDirection.Input;
                        comm.Parameters["@LoaiVe"].Value = NguoiLonhayTreEm;

                        comm.Parameters.Add(new SqlParameter("@output", SqlDbType.VarChar, 100));
                        comm.Parameters["@output"].Direction = ParameterDirection.Output;
                        comm.Parameters["@output"].Value = "";
                        comm.ExecuteNonQuery();
                        string result = comm.Parameters["@output"].Value.ToString();
                        MessageBox.Show(result, "Thông Báo");
                        loadDataLostUpdateFix();
                    }
                }
                comm.Dispose();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông Báo");
            }
        }

        // tìm kiếm vé theo điều kịện
        private void btn_search_Click(object sender, EventArgs e)
        {
            loadDataLostUpdateError();
          
        }
        // tìm kiếm vé theo điều kịện
        private void btn_search_fix_Click(object sender, EventArgs e)
        {
            loadDataLostUpdateFix();
        }
        // tìm kiếm vé theo điều kịện
        private void btn_search_deadlock_error_Click(object sender, EventArgs e)
        {
            loadDataLostUpdateDeadlockError();
        }
        //thực hiện hành động đặt vé
        private void btn_update_deadlock_error_Click(object sender, EventArgs e)
        {
            try
            {
                int n = dgw_lostupdate_deadlock_error.Rows.Count - 1;
                SqlConnection connect = Connection.CreateConnectionString();
                SqlCommand comm = new SqlCommand();

                comm.Connection = connect;
                for (int i = 0; i < n; i++)
                {


                    if (dgw_lostupdate_deadlock_error.Rows[i].Cells[0].Value.Equals("1"))
                    {

                        string maVe = dgw_lostupdate_deadlock_error.Rows[i].Cells[2].Value.ToString();
                        string TinhTrang = dgw_lostupdate_deadlock_error.Rows[i].Cells[4].Value.ToString();
                        string MaHD = dgw_lostupdate_deadlock_error.Rows[i].Cells[6].Value.ToString();
                        string NguoiLonhayTreEm = dgw_lostupdate_deadlock_error.Rows[i].Cells[5].Value.ToString();
                        comm.CommandText = "sp_LostUpdateDeadlock_erorr";
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.Add(new SqlParameter("@MaVe", SqlDbType.VarChar, 10));
                        comm.Parameters["@MaVe"].Direction = ParameterDirection.Input;
                        comm.Parameters["@MaVe"].Value = maVe;

                        comm.Parameters.Add(new SqlParameter("@LoaiVe", SqlDbType.Bit));
                        comm.Parameters["@LoaiVe"].Direction = ParameterDirection.Input;
                        comm.Parameters["@LoaiVe"].Value = NguoiLonhayTreEm;

                        comm.Parameters.Add(new SqlParameter("@output", SqlDbType.VarChar, 100));
                        comm.Parameters["@output"].Direction = ParameterDirection.Output;
                        comm.Parameters["@output"].Value = "";
                        comm.ExecuteNonQuery();
                        string result = comm.Parameters["@output"].Value.ToString();
                        MessageBox.Show(result, "Thông Báo");
                        loadDataLostUpdateDeadlockError();
                    }
                }
                comm.Dispose();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông Báo");
            }

        }

        private void btn_cancle_deadlock_error_Click(object sender, EventArgs e)
        {
            this.Close();

        }
    }
}
